﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_ListLatestAnnouncement')
BEGIN
    PRINT 'Dropping Procedure proc_cm_ListLatestAnnouncement'
    DROP  Procedure  proc_cm_ListLatestAnnouncement
END
GO

PRINT 'Creating Procedure proc_cm_ListLatestAnnouncement'
GO

CREATE PROCEDURE [dbo].[proc_cm_ListLatestAnnouncement]
	@pSectionCd nvarchar(50) = 'Main'
	, @pCount int = 100
AS
BEGIN
	SET NOCOUNT ON

	SELECT TOP(@pCount)
		[announcement_id]
		,[html_page_id]
		,[announcement_title_txt]
		,[announcement_txt]
		,[effective_start_dttm]
		,[effective_end_dttm]
		,[create_dttm]
		,[update_dttm]
		,ISNULL([effective_start_dttm], [create_dttm]) as [display_dttm]
	FROM
		[dbo].[tbl_announcement] (NOLOCK)
	WHERE
		[section_cd] = @pSectionCd
		and (
			([effective_start_dttm] is null and [effective_end_dttm] is null)
			or ([effective_start_dttm] is null and GETDATE() < [effective_end_dttm])
			or ([effective_end_dttm] is null and GETDATE() > [effective_start_dttm])
			or (getdate() between [effective_start_dttm] and [effective_end_dttm])
		)
	ORDER BY
		ISNULL([effective_start_dttm], [create_dttm]) DESC
END
GO

GRANT EXEC ON dbo.proc_cm_ListLatestAnnouncement TO PUBLIC
GO
